/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.com.jsepc.rep.report;

import cn.com.jsepc.rep.catalog.CatalogDAO;
import cn.com.jsepc.rep.comm.ConstUtil;
import cn.com.jsepc.rep.vo.Relate_RepVO;
import cn.com.jsepc.rep.vo.ReportVO;
import com.jarp.db.DAO;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import net.sf.json.JSONArray;

/**
 *
 * @author 赵远
 */
public class GetRepPara {
      public String get_rep_para(String start, String limit,String repid) {
        String res = "";
        DAO dao = new DAO();
        try {
            List<Relate_RepVO> lstResults = new ArrayList<Relate_RepVO>();
            int iStart = Integer.parseInt(start);
            int iPageSize = Integer.parseInt(limit);
            int iPage = iStart / iPageSize + 1;
            StringBuffer str = new StringBuffer();
            str.append("select b.repid,b.repcode,b.repname,c.CATANAME from (select * from TB_REP_PARA a where a.REPID="+repid+") a " +
            		" left join TB_REPORT b on b.REPID=a.repparaid " +
            		" left join TB_CATALOG c on c.cataid=b.cataid");
            System.out.println("str:"+str.toString());
            Vector v = dao.getDataSet(ConstUtil.DBNAME, str.toString(), iPage, iPageSize);
            if (v != null) {
                for (int i = 0; i < v.size(); i++) {
                    Hashtable hashRows = (Hashtable) v.get(i);
                    Relate_RepVO repVO = new Relate_RepVO();
                    repVO.setRepid((String) hashRows.get("REPID"));
                    repVO.setRepcode((String) hashRows.get("REPCODE"));
                    repVO.setRepname((String) hashRows.get("REPNAME"));
                    repVO.setCataname((String) hashRows.get("CATANAME"));
                    lstResults.add(repVO);
                }
            }
            JSONArray json = new JSONArray();
            StringBuffer str1 = new StringBuffer();
            str1.append("select count(1) CNT from TB_REP_PARA where repid="+repid);
            String totalCount = dao.getDataValue(ConstUtil.DBNAME, str1.toString());
            json = JSONArray.fromObject(lstResults);
            res = "{totalCount:" + totalCount + ",data:" + json.toString()
                    + "}";
        } catch (Exception ex) {
            ex.printStackTrace();
            System.out.println("NewProjectTree getXmDl Exception.");
        }
        return res;
    }
      
      public String get_rep_para_fac(String start, String limit,String repid) {
          String res = "";
          DAO dao = new DAO();
          try {
              List<Relate_RepVO> lstResults = new ArrayList<Relate_RepVO>();
              int iStart = Integer.parseInt(start);
              int iPageSize = Integer.parseInt(limit);
              int iPage = iStart / iPageSize + 1;
              StringBuffer str = new StringBuffer();
              str.append("select b.repid,b.repcode,b.repname,c.CATANAME from (select * from TB_REP_PARA a where a.REPID="+repid+") a " +
              		" left join TB_REPORT b on b.REPID=a.repparaid " +
              		" left join TB_CATALOG c on c.cataid=b.cataid where b.sourceType = 2 and b.ISVALID=1 and b.REFFACT is not null ");
              System.out.println("str:"+str.toString());
              Vector v = dao.getDataSet(ConstUtil.DBNAME, str.toString(), iPage, iPageSize);
              if (v != null) {
                  for (int i = 0; i < v.size(); i++) {
                      Hashtable hashRows = (Hashtable) v.get(i);
                      Relate_RepVO repVO = new Relate_RepVO();
                      repVO.setRepid((String) hashRows.get("REPID"));
                      repVO.setRepcode((String) hashRows.get("REPCODE"));
                      repVO.setRepname((String) hashRows.get("REPNAME"));
                      repVO.setCataname((String) hashRows.get("CATANAME"));
                      lstResults.add(repVO);
                  }
              }
              JSONArray json = new JSONArray();
              StringBuffer str1 = new StringBuffer();
              str1.append("select count(1) from (select * from TB_REP_PARA a where a.REPID="+repid+") a " +
                		" left join TB_REPORT b on b.REPID=a.repparaid " +
                		" left join TB_CATALOG c on c.cataid=b.cataid where b.sourceType = 2 and b.ISVALID=1 and b.REFFACT is not null ");
              String totalCount = dao.getDataValue(ConstUtil.DBNAME, str1.toString());
              json = JSONArray.fromObject(lstResults);
              res = "{totalCount:" + totalCount + ",data:" + json.toString()
                      + "}";
          } catch (Exception ex) {
              ex.printStackTrace();
              System.out.println("NewProjectTree getXmDl Exception.");
          }
          return res;
      }
      
      public String get_para_report(String start, String limit, String KeyWord_0,
            String KeyWord_1, String KeyWord_2, String KeyWord_3,
            String KeyWord_4, String KeyWord_5, String repid){
          String res="";
          
           DAO dao = new DAO();
        try {
            List<ReportVO> lstResults = new ArrayList<ReportVO>();
            int iStart = Integer.parseInt(start);
            int iPageSize = Integer.parseInt(limit);
            int iCurPage = iStart / iPageSize + 1;
            StringBuffer str = new StringBuffer();
            str.append("select a.*,c.cataname,e.name  from (select * from TB_REPORT a where sourceType = 2 and ISVALID=1 and repid!="+repid); 

            if (KeyWord_5 != null && !KeyWord_5.equals("")) {
                //点击树根节点的情况
                if (KeyWord_5.equals("0")) {
                    if (KeyWord_0 != null && !KeyWord_0.equals("")) {
                        str.append(" and a.REPNAME like '%" + KeyWord_0 + "%'");
                    }
                    if (KeyWord_1 != null && !KeyWord_1.equals("") && !KeyWord_1.equals("101")) {
                        str.append(" and a.DEPARTMENT = '" + KeyWord_1 + "'");
                    }
                    if (KeyWord_2 != null && !KeyWord_2.equals("")) {
                        if (KeyWord_2.equals("3")) {
                        } else {
                            str.append(" and a.SOURCETYPE = " + KeyWord_2);
                        }
                    }
                    if (KeyWord_3 != null && !KeyWord_3.equals("")) {
                        if (KeyWord_3.equals("6")) {
                        } else {
                            str.append(" and a.DATATYPE = " + KeyWord_3);
                        }
                    }
                    if (KeyWord_4 != null && !KeyWord_4.equals("")) {
                        if (KeyWord_4.equals("7")) {
                        } else {
                            str.append(" and a.UPDATETIME = " + KeyWord_4);
                        }
                    }
                } //点击树子节点的情况
                else {
                    if (KeyWord_0 != null && !KeyWord_0.equals("")) {
                        str.append(" and a.REPNAME like '%" + KeyWord_0 + "%'");
                    }
                    if (KeyWord_1 != null && !KeyWord_1.equals("") && !KeyWord_1.equals("101")) {
                        str.append(" and a.DEPARTMENT = '" + KeyWord_1 + "' ");
                    }
                    if (KeyWord_2 != null && !KeyWord_2.equals("")) {
                        if (KeyWord_2.equals("3")) {
                        } else {
                            str.append(" and a.SOURCETYPE = " + KeyWord_2);
                        }
                    }
                    if (KeyWord_3 != null && !KeyWord_3.equals("")) {
                        if (KeyWord_3.equals("6")) {
                        } else {
                            str.append(" and a.DATATYPE = " + KeyWord_3);
                        }
                    }
                    if (KeyWord_4 != null && !KeyWord_4.equals("")) {
                        if (KeyWord_4.equals("7")) {
                        } else {
                            str.append(" and a.UPDATETIME = " + KeyWord_4);
                        }
                    }
                    if (KeyWord_5 != null && !KeyWord_5.equals("")) {
                       // str.append(" and a.CATAID = " + KeyWord_5);
                    	String catid = new CatalogDAO().getAllCatatID(KeyWord_5);
    					str.append(" and a.CATAID in ( " + catid+") ");
                    }
                }
            } //未点击树的情况
            else {
                if (KeyWord_0 != null && !KeyWord_0.equals("")) {
                    str.append(" and REPNAME like '%" + KeyWord_0 + "%'");
                }
                if (KeyWord_1 != null && !KeyWord_1.equals("") && !KeyWord_1.equals("101")) {
                    str.append(" and DEPARTMENT = '" + KeyWord_1 + "'");
                }
                if (KeyWord_2 != null && !KeyWord_2.equals("")) {
                    if (KeyWord_2.equals("3")) {
                    } else {
                        str.append(" and SOURCETYPE = " + KeyWord_2);
                    }
                }
                if (KeyWord_3 != null && !KeyWord_3.equals("")) {
                    if (KeyWord_3.equals("6")) {
                    } else {
                        str.append(" and DATATYPE = " + KeyWord_3);
                    }
                }
                if (KeyWord_4 != null && !KeyWord_4.equals("")) {
                    if (KeyWord_4.equals("7")) {
                    } else {
                        str.append(" and UPDATETIME = " + KeyWord_4);
                    }
                }
//                if (KeyWord_5 != null && !KeyWord_5.equals("")) {
//                    str.append(" and a.CATAID = " + KeyWord_5); 
//                }
            }
            str.append(" ) a");
       //     str.append(" left join tb_rep_para b on a.repid=b.repparaid and b.repparaid="+repid);
            str.append(" left join TB_CATALOG c on c.CATAID = a.CATAID");
            str.append(" left join TB_CORP_DEPT e on e.ID=a.DEPARTMENT ");

//			str.append(" where rn between " + iStart + " and " + iEnd);
            System.out.println(str.toString());
            Vector v = dao.getDataSet(ConstUtil.DBNAME, str.toString(), iCurPage, iPageSize);
//            System.out.println("vector:" + v);
            if (v != null) {
                for (int i = 0; i < v.size(); i++) {
                    Hashtable hashRows = (Hashtable) v.get(i);
                    ReportVO repVO = new ReportVO();
                    repVO.setRepid((String) hashRows.get("REPID"));
                    repVO.setRepcode((String) hashRows.get("REPCODE"));
                    repVO.setRepname((String) hashRows.get("REPNAME"));
                    repVO.setCataid((String) hashRows.get("CATANAME"));
                    repVO.setRepdesc((String) hashRows.get("REPDESC"));
                    repVO.setDesc((String) hashRows.get("CALDESC"));
                    repVO.setDataprec((String) hashRows.get("DATAPREC"));
                    repVO.setDataunit((String) hashRows.get("DATAUNIT"));
                    repVO.setDepartmentid((String) hashRows.get("DEPARTMENT"));
                    repVO.setDepartment((String) hashRows.get("NAME"));
                    repVO.setSourcetype((String) hashRows.get("SOURCETYPE"));
                    repVO.setDatatype((String) hashRows.get("DATATYPE"));
                    repVO.setUpdatetime((String) hashRows.get("UPDATETIME"));
                    repVO.setReffact((String) hashRows.get("REFFACT"));
                    repVO.setRepvaluecol((String) hashRows.get("REPVALUECOL"));
                   // repVO.setIsselect((String) hashRows.get("REPPARAID"));
                    lstResults.add(repVO);

                }
            }
            JSONArray json = new JSONArray();
            StringBuffer str1 = new StringBuffer();
			str1.append("select count(1) CNT from (");
			str1.append(str);
			str1.append(" ) z ");
            String totalCount = dao.getDataValue(ConstUtil.DBNAME, str1.toString());
            json = JSONArray.fromObject(lstResults);
            res = "{totalCount:" + totalCount + ",data:" + json.toString()
                    + "}";
            //System.out.println(res);
        } catch (Exception ex) {
            ex.printStackTrace();
            System.out.println("NewProjectTree getXmDl Exception.");
        }
          return res;
      }
      
      public String updata_rep_para(String my_id_array,String repid){
          String res="";
          String [] getrepid=my_id_array.split(",");
          DAO dao=new DAO();
        ArrayList<String> list=new ArrayList<String>();
        ArrayList<String> list_temp=new ArrayList<String>();
          System.out.println("list:"+list);
          
        for(int i=0;i<getrepid.length;i++){
           boolean b=this.repidExit(repid,getrepid[i]);
           if(b==true){
            String insert_sql="insert into tb_rep_para(repid,repparaid) values ("+repid+","+getrepid[i]+")";
             list.add(insert_sql);
           }
        }
          System.out.println("list.equals(list_temp)="+list.equals(list_temp));
        try {
            if(list.equals(list_temp)){
                res="ok";
            }
            else{
                
                boolean b=dao.execCommand(ConstUtil.DBNAME, list);
            if(b==true){
                res="ok";
            }
            else{
                res="wrong";
            }
            }
        } catch (ServletException ex) {
            Logger.getLogger(GetRepPara.class.getName()).log(Level.SEVERE, null, ex);
        }
          return res;
      }
      
      public boolean repidExit(String repid,String repparaid){
          boolean b=false;
          String isExist="select * from tb_rep_para where repid="+repid+" and repparaid="+repparaid;
          DAO dao=new DAO();
        try {
            Vector v=dao.getDataSet(ConstUtil.DBNAME, isExist);
            if(v==null){
                b=true;
            }
            else{
                b=false;
            }
        } catch (ServletException ex) {
            Logger.getLogger(GetRepPara.class.getName()).log(Level.SEVERE, null, ex);
        }
          return b;
      }
      
      public String delete_rep_para(String repparaid,String repidarray){
          String res="";
          DAO dao=new DAO();
          String []repid=repidarray.split(",");
          ArrayList<String> list=new ArrayList<String>();
          for(int i=0;i<repid.length;i++){
              String delete_sql="delete from tb_rep_para where repid="+repparaid+" and repparaid="+repid[i];
              list.add(delete_sql);
          }
        try {
            boolean b=dao.execCommand(ConstUtil.DBNAME, list);
            if(b==true){
                res="ok";
            }
            else{
                res="wrong";
            }
        } catch (ServletException ex) {
            Logger.getLogger(GetRepPara.class.getName()).log(Level.SEVERE, null, ex);
        }
          return res;
      }
}
